package com.yitong.base.util;

/**
 * Jun 25, 2012
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.annotation.Order;

/**
 * Excel表格导入 收件人地址信息
 * 
 * @author
 * @version 1.0
 * @since 1.0
 */
public abstract class ExcelHelper {

	/**
	 * Excel 2003
	 */
	private final static String XLS = "xls";
	/**
	 * Excel 2007
	 */
	private final static String XLSX = "xlsx";

	/**
	 * 由Excel文件的Sheet导出至List
	 * 
	 * @param file
	 * @param sheetNum
	 * @return
	 */
	public static List<?> exportListFromExcel(File file, int sheetNum) throws IOException {
		//return exportListFromExcel(new FileInputStream(file), FilenameUtils.getExtension(file.getName()), sheetNum);
		return null;
	}

	/**
	 * 由Excel流的Sheet导出至List
	 * 
	 * @param is
	 * @param extensionName
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
	public static List<?> exportListFromExcel(InputStream is, String extensionName, int sheetNum) throws IOException {

		Workbook workbook = null;

		if (extensionName.toLowerCase().equals(XLS)) {
			workbook = new HSSFWorkbook(is);
		} else if (extensionName.toLowerCase().equals(XLSX)) {
			workbook = new XSSFWorkbook(is);
		}

		return exportListFromExcel(workbook, sheetNum);
	}

	/**
	 * 由指定的Sheet导出至List
	 * 
	 * @param workbook
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
	private static List<?> exportListFromExcel(Workbook workbook, int sheetNum) {

		Sheet sheet = workbook.getSheetAt(sheetNum);
		// 解析公式结果
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		List<Order> list = new ArrayList<Order>();
		// 获取最小，最大行号
		int minRowIx = sheet.getFirstRowNum() + 1;
		int maxRowIx = sheet.getLastRowNum();
		// 循环每一行，每一行数据都添加到list集合中
		for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
			// 定义对象
//			Order order = new Order();
			// 循环拿到每一行数据
			Row row = sheet.getRow(rowIx);
			// 获取第一个，最后一个单元格的序号
			short minColIx = row.getFirstCellNum();
			short maxColIx = row.getLastCellNum();
			for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
				// 通过单元格的序号，拿到一行中每个单元格的数据
				Cell cell = row.getCell(new Integer(colIx));
				CellValue cellValue = evaluator.evaluate(cell);
				if (cellValue == null) {
					continue;
				}
				// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
				// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
				switch (cellValue.getCellType()) {
				case Cell.CELL_TYPE_BOOLEAN:
					String boolvalue = cellValue.getBooleanValue() + "";
					switch (colIx) {
					case 0:
//						order.setReceiverName(boolvalue);
						break;
					case 1:
//						order.setReceiverTelephone(boolvalue);
						break;
					case 2:
//						order.setToAddress(boolvalue);
						break;
					case 3:
						// order.setContent(boolvalue);
						break;
					default:
						break;
					}
					break;
				case Cell.CELL_TYPE_NUMERIC:
					// 这里的日期类型会被转换为数字类型，需要判别后区分处理
					if (DateUtil.isCellDateFormatted(cell)) {
						String datevalue = cell.getDateCellValue() + "";
						switch (colIx) {
						case 0:
//							order.setReceiverName(datevalue);
							break;
						case 1:
//							order.setReceiverTelephone(datevalue);
							break;
						case 2:
//							order.setToAddress(datevalue);
							break;
						case 3:
							// order.setContent(datevalue);
							break;
						default:
							break;
						}
					} else {
						long datevalue2 = new Double(cellValue.getNumberValue()).longValue();
						String datevalue = Long.toString(datevalue2);
						switch (colIx) {
						case 0:
//							order.setReceiverName(datevalue);
							break;
						case 1:
//							order.setReceiverTelephone(datevalue);
							break;
						case 2:
//							order.setToAddress(datevalue);
							break;
						case 3:
							// order.setContent(datevalue);
							break;
						default:
							break;
						}
					}
					break;
				case Cell.CELL_TYPE_STRING:
					String stringvalue = cellValue.getStringValue() + "";
					switch (colIx) {
					case 0:
//						order.setReceiverName(stringvalue);
						break;
					case 1:
//						order.setReceiverTelephone(stringvalue);
						break;
					case 2:
//						order.setToAddress(stringvalue);
						break;
					case 3:
						// order.setContent(stringvalue);
						break;
					default:
						break;
					}
					break;
				case Cell.CELL_TYPE_FORMULA:
					break;
				case Cell.CELL_TYPE_BLANK:
					break;
				case Cell.CELL_TYPE_ERROR:
					break;
				default:
					break;
				}
			}
			//list.add(order);
		}
		return list;
	}
}
